package cn.pengpeng.sql;

/**
新增用户

思路：
1：抽取出来当天活跃用户 etl_user_active_day,
2: 拿当天活跃用户跟历史用户做对比（得出当日的新增用户）
3：增加新增用户到历史记录表里面
4：对当日新增用户进行各维度统计

          历史用户表          
create table etl_history_user(uid string);

           创建一个每日新增用户表           
create table etl_user_new_day(
   uid string,
   commit_time string,
   city string,
   release_channel string,
   app_ver_name string
)partitioned by (day string);



           创建维度聚合的           
create table dim_user_new(
   city string,
   release_channel string,
   app_ver_name string,
   newnew_cnt int
)partitioned by (day string,flag string);

           etl_user_new_day  *        

          新增用户  20170101          
insert into table etl_user_new_day partition(day="20170101")
select 
   a.uid,a.commit_time,a.city,a.release_channel,a.app_ver_name
from etl_user_active_day a
left join
etl_history_user b
on a.uid=b.uid
where a.day="20170101" and b.uid is null;


         把新增用户插入到历史记录表里面         
insert into table etl_history_user
select uid from etl_user_new_day where day="20170101";

          新增用户  20170102          
insert into table etl_user_new_day partition(day="20170102")
select 
   a.uid,a.commit_time,a.city,a.release_channel,a.app_ver_name
from etl_user_active_day a
left join
etl_history_user b
on a.uid=b.uid
where a.day="20170102" and b.uid is null;


         把新增用户插入到历史记录表里面         
insert into table etl_history_user
select uid from etl_user_new_day where day="20170102";

         分维度进行统计         
            分维度进行统计           
时间	城市	渠道	版本	新增用户
当天	0		0		0 
当天	0		0		1
当天	0		1		0
当天	0		1		1
当天	1		0		0
当天	1		0		1
当天	1		1		0
当天	1		1		1
from etl_user_new_day 

insert into table dim_user_new partition(day="20170101",flag="000")
select "all","all","all",count(1) where day="20170101"

insert into table dim_user_new partition(day="20170101",flag="001")
select "all","all",app_ver_name,count(1) where day="20170101"
group by app_ver_name

insert into table dim_user_new partition(day="20170101",flag="010")
select "all",release_channel,"all",count(1) where day="20170101"
group by release_channel

insert into table dim_user_new partition(day="20170101",flag="011")
select "all",release_channel,app_ver_name,count(1) where day="20170101"
group by release_channel,app_ver_name

insert into table dim_user_new partition(day="20170101",flag="100")
select city,"all","all",count(1) where day="20170101"
group by city

insert into table dim_user_new partition(day="20170101",flag="101")
select city,"all",app_ver_name,count(1) where day="20170101"
group by city , app_ver_name

insert into table dim_user_new partition(day="20170101",flag="110")
select city,release_channel,"all",count(1) where day="20170101"
group by city,release_channel

insert into table dim_user_new partition(day="20170101",flag="111")
select city,release_channel,app_ver_name,count(1) where day="20170101"
group by city,release_channel,app_ver_name
;

每天的重复工作，脚本执行

**/

public class Xinzen {

}
